package com.yalu.file;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.yalu.enmus.CurrencyEnum;
import com.yalu.entity.BillData;
import com.yalu.entity.BillDetailData;
import com.yalu.entity.BillDetailDataExcel;
import com.yalu.util.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.pdfbox.io.RandomAccessFile;
import org.apache.pdfbox.pdfparser.PDFParser;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.text.PDFTextStripper;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 汇丰
 */
@Slf4j
@Component
public class PdfRead {

    /**
     * 读PDF文件，使用了pdfbox开源项目
     *
     * @param fileName
     */
    public static void readPdf(String fileName, String year, Map<String, BillData> map) {
        File file = new File(fileName);
        FileInputStream in = null;
        try {
            in = new FileInputStream(fileName);
            // 新建一个PDF解析器对象
            PDFParser parser = new PDFParser(new RandomAccessFile(file, "r"));
            // 对PDF文件进行解析
            parser.parse();
            // 获取解析后得到的PDF文档对象
            PDDocument pdfdocument = parser.getPDDocument();
            int pageSize = pdfdocument.getNumberOfPages();
            Map<String, String> monthMap = new HashMap<>(12);
            monthMap.put("Jan", "01");
            monthMap.put("Feb", "02");
            monthMap.put("Mar", "03");
            monthMap.put("Apr", "04");
            monthMap.put("May", "05");
            monthMap.put("Jun", "06");
            monthMap.put("Jul", "07");
            monthMap.put("Aug", "08");
            monthMap.put("Sep", "09");
            monthMap.put("Oct", "10");
            monthMap.put("Nov", "11");
            monthMap.put("Dec", "12");
            for (int i = 0; i < pageSize; i++) {
                // 新建一个PDF文本剥离器
                PDFTextStripper stripper = new PDFTextStripper();
                // 设置按顺序输出
                stripper.setSortByPosition(true);
                stripper.setStartPage(i + 1);
                stripper.setEndPage(i + 1);
                String lineSepartor = stripper.getLineSeparator();
                // 从PDF文档对象中剥离文本
                String result = stripper.getText(pdfdocument);
                String[] resultArray = result.split(lineSepartor);
                int arrayLength = resultArray.length;
                Queue<BillDetailData> dataQueue = new LinkedList<>();
                Queue<String> nowQueue = new LinkedList<>();
                int orderNumber = 1;
                for (int y = 0; y < arrayLength; y++) {
                    String lineText = resultArray[y];
                    if (null == lineText || "".equals(lineText.trim()) || lineText.contains("Total")) {
                        continue;
                    }
                    if (lineText.contains("Thank you")) {
                        break;
                    }
                    if (lineText.contains("HSBC Business Direct HKD Current") || lineText.contains("BusinessVantage HKD Current")) {
                        nowQueue.poll();
                        orderNumber = 1;
                        y++;
                        BillData billData = map.get("HKDCurrent");
                        if (null == billData) {
                            billData = new BillData();
                            billData.setCurrency("HKD");
                            billData.setType("Current");
                            List<BillDetailData> list = new ArrayList<>();
                            billData.setDetailData(list);
                            map.put(billData.getCurrency().concat(billData.getType()), billData);
                        }
                        String key = billData.getCurrency().concat(billData.getType());
                        nowQueue.offer(key);
                    } else if (lineText.contains("HSBC Business Direct HKD Savings") || lineText.contains("BusinessVantage HKD Savings")) {
                        nowQueue.poll();
                        orderNumber = 1;
                        y++;
                        BillData billData = map.get("HKDSavings");
                        if (null == billData) {
                            billData = new BillData();
                            billData.setCurrency("HKD");
                            billData.setType("Savings");
                            List<BillDetailData> list = new ArrayList<>();
                            billData.setDetailData(list);
                            map.put(billData.getCurrency().concat(billData.getType()), billData);
                        }
                        String key = billData.getCurrency().concat(billData.getType());
                        nowQueue.offer(key);
                    } else if (lineText.contains("HSBC Business Direct Foreign Currency Savings") || lineText.contains("BusinessVantage Foreign Currency Savings")) {
                        nowQueue.poll();
                        String ccyLineText = resultArray[y + 1];
                        if (ccyLineText.contains("CCY Date Transaction Details Deposit Withdrawal Balance")) {
                            y = y + 2;
                            String keylineText = resultArray[y];
                            if (keylineText.contains("Total")) {
                                continue;
                            }
                            boolean isData = true;
                            while (isData) {
                                Pattern pattern = Pattern.compile(CommonUtil.AMOUNT_MATCHES);
                                Matcher matcher = pattern.matcher(keylineText.replace(",", ""));
                                if (!matcher.find() && (y + 1) < resultArray.length) {
                                    keylineText = keylineText + resultArray[y + 1];
                                    y++;
                                } else {
                                    isData = false;
                                }
                            }
                            String[] keylineTextArray = keylineText.split(" ");
                            String currency = keylineTextArray[0];
                            BillData billData = new BillData();
                            billData.setCurrency(currency);
                            billData.setType("Savings");
                            String key = billData.getCurrency().concat(billData.getType());
                            BillData mapBillData = map.get(key);
                            if (null == mapBillData) {
                                orderNumber = 1;
                                List<BillDetailData> list = new ArrayList<>();
                                billData.setDetailData(list);
                                map.put(key, billData);
                            } else {
                                billData = mapBillData;
                            }
                            BillDetailData detailData = new BillDetailData();
                            int length = keylineTextArray.length;
                            String day = length > 1 ? keylineTextArray[1] : "";
                            String month = length > 2 ? monthMap.get(keylineTextArray[2]) : "";
                            detailData.setDate(year + "/" + month + "/" + day);
                            detailData.setOrderNumber(orderNumber);
                            String one = keylineTextArray[length - 1].replace(",", "").replace("OD", "");
                            if (one.contains("DR")) {
                                one = "-" + one.replace("DR", "");
                            }
                            String transactionDetails = handleTransactionType(keylineText);
                            if (!StringUtils.hasText(transactionDetails)) {
                                log.warn("未找到交易类型：{}", keylineText);
                                continue;
                            }
                            int isTwo = length - 2;
                            String two = "";
                            if (isTwo >= 0) {
                                two = keylineTextArray[isTwo].replace(",", "");
                            }

                            handleAmount(keylineText, one, transactionDetails, detailData, two);
                            billData.getDetailData().add(detailData);
                            dataQueue.poll();
                            dataQueue.offer(detailData);
                            orderNumber++;
                            nowQueue.offer(key);
                        }
                    } else {
                        String key = nowQueue.poll();
                        if (null != key) {
                            nowQueue.offer(key);

                            if (null != lineText) {
                                boolean isData = true;
                                while (isData) {
                                    Pattern pattern = Pattern.compile(CommonUtil.AMOUNT_MATCHES);
                                    Matcher matcher = pattern.matcher(lineText.replace(",", ""));
                                    if (!matcher.find() && (y + 1) < resultArray.length) {
                                        lineText = lineText + resultArray[y + 1];
                                        y++;
                                    } else {
                                        isData = false;
                                    }
                                }
                                String[] lineTextArray = lineText.split(" ");
                                int length = lineTextArray.length;
                                String one = lineTextArray[length - 1].replace(",", "").replace("OR", "");
                                if (one.contains("DR")) {
                                    one = "-" + one.replace("DR", "");
                                }
                                String transactionDetails = handleTransactionType(lineText);
                                if (!StringUtils.hasText(transactionDetails)) {
                                    log.warn("未找到交易类型：{}", lineText);
                                    continue;
                                }
                                BillDetailData detailDataQueue = dataQueue.poll();
                                dataQueue.offer(detailDataQueue);
                                BillDetailData detailData = new BillDetailData();
                                if (null != detailDataQueue) {
                                    dataQueue.poll();
                                    BeanUtils.copyProperties(detailDataQueue, detailData);
                                    dataQueue.offer(detailData);
                                }
                                if (StringUtils.hasText(lineTextArray[0])) {
                                    String text = length > 1 ? lineTextArray[0] : "";
                                    if (text.matches("[1-9]|[0-9]{2}")) {
                                        String day = text;
                                        String month = length > 2 ? monthMap.get(lineTextArray[1]) : "";
                                        String date = year + "/" + month + "/" + day;
                                        detailData.setDate(date);
                                    } else if (text.matches("[A-Z]{3}") && CurrencyEnum.isCurrency(text)) {
                                        String currency = text;
                                        detailData = new BillDetailData();
                                        BillData billData = new BillData();
                                        billData.setCurrency(currency);
                                        billData.setType(key.substring(3));
                                        key = billData.getCurrency().concat(billData.getType());
                                        BillData nowBillData = map.get(key);
                                        if (null == nowBillData) {
                                            orderNumber = 1;
                                            List<BillDetailData> list = new ArrayList<>();
                                            billData.setDetailData(list);
                                            map.put(key, billData);
                                        }
                                        String day = length > 2 ? lineTextArray[1] : "";
                                        String month = length > 3 ? monthMap.get(lineTextArray[2]) : "";
                                        String date = year + "/" + month + "/" + day;
                                        detailData.setDate(date);
                                        dataQueue.poll();
                                        nowQueue.poll();
                                        nowQueue.offer(key);
                                        dataQueue.offer(detailData);
                                    }
                                }
                                detailData.setOrderNumber(orderNumber);
                                int isTwo = length - 2;
                                String two = "";
                                if (isTwo >= 0) {
                                    two = lineTextArray[isTwo].replace(",", "");
                                }
                                handleAmount(lineText, one, transactionDetails, detailData, two);
                                BillData billData = map.get(key);
                                if (null != billData && StringUtils.hasText(detailData.getTransactionDetails()) && (StringUtils.hasText(detailData.getBalance()) || StringUtils.hasText(detailData.getDeposit()) || StringUtils.hasText(detailData.getWithdrawal()))) {
                                    billData.getDetailData().add(detailData);
                                    dataQueue.poll();
                                    dataQueue.offer(detailData);
                                }
                                orderNumber++;
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("读取PDF文件" + file.getAbsolutePath() + "生失败！" + e);
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e1) {
                }
            }
        }
    }

    /**
     * 处理交易类型
     *
     * @param lineText
     * @return
     */
    private static String handleTransactionType(String lineText) {
        String transactionDetails = null;
        if (lineText.contains("DEPOSIT BIB- BALANCE")) {
            transactionDetails = "DEPOSIT BIB- BALANCE";
        } else if (lineText.contains("BALANCE")) {
            transactionDetails = "BALANCE";
        } else if (lineText.contains("DEPOSIT CORRECTION")) {
            transactionDetails = "DEPOSIT CORRECTION";
        } else if (lineText.contains("PAYPAL")) {
            transactionDetails = "PAYPAL";
        } else if (lineText.contains("DROP-IN BOX CHQ DEPOSIT")) {
            transactionDetails = "DROP-IN BOX CHQ DEPOSIT";
        } else if (lineText.contains("REM HKD21838.92")) {
            transactionDetails = "REM HKD21838.92";
        } else if (lineText.contains("WITHDRAWAL CORRECTION")) {
            transactionDetails = "WITHDRAWAL CORRECTION";
        } else if (lineText.contains("WITHDRAWAL")) {
            transactionDetails = "WITHDRAWAL";
        } else if (lineText.contains("DEPOSIT")) {
            transactionDetails = "DEPOSIT";
        } else if (lineText.contains("REMIT MKTG CAMP")) {
            transactionDetails = "REMIT MKTG CAMP";
        } else if (lineText.contains("REM")) {
            transactionDetails = "REM";
        } else if (lineText.contains("CREDIT INTEREST")) {
            transactionDetails = "CREDIT INTEREST";
        } else if (lineText.contains("CHARGES")) {
            transactionDetails = "CHARGES";
        } else if (lineText.contains("GOLD/EXCHANGE DEBIT")) {
            transactionDetails = "GOLD/EXCHANGE DEBIT";
        } else if (lineText.contains("FROM")) {
            transactionDetails = "FROM";
        } else if (lineText.contains("CREDIT AS ADVISED")) {
            transactionDetails = "CREDIT AS ADVISED";
        } else if (lineText.contains("CR TO")) {
            transactionDetails = "CR TO";
        } else if (lineText.contains("AUDIT CONFRMTION FEE")) {
            transactionDetails = "AUDIT CONFRMTION FEE";
        } else if (lineText.contains("TT REBATE")) {
            transactionDetails = "TT REBATE";
        } else if (lineText.contains("ACCT MAINT FEE")) {
            transactionDetails = "ACCT MAINT FEE";
        } else if (lineText.contains("DELIVERY CHARGE")) {
            transactionDetails = "DELIVERY CHARGE";
        } else if (lineText.contains("BLUBAN ELECTRON")) {
            transactionDetails = "BLUBAN ELECTRON";
        } else if (lineText.contains("NB")) {
            transactionDetails = "NB";
        } else if (lineText.contains("REBATE")) {
            transactionDetails = "REBATE";
        } else if (lineText.contains("SUNRATE")) {
            transactionDetails = "SUNRATE";
        } else if (lineText.contains("ISSUE OR REPLACE2")) {
            transactionDetails = "ISSUE OR REPLACE2";
        } else if (lineText.contains("DEP94241117-16")) {
            transactionDetails = "DEP94241117-16";
        } else if (lineText.contains("PAYPAL HK LTD")) {
            transactionDetails = "PAYPAL HK LTD";
        } else if (lineText.contains("MONTHLY SERVICE FEE")) {
            transactionDetails = "MONTHLY SERVICE FEE";
        } else if (lineText.contains("BIB SEC DEVICE FEE")) {
            transactionDetails = "BIB SEC DEVICE FEE";
        } else if (lineText.contains("CHEQUE")) {
            transactionDetails = "CHEQUE";
        } else if (lineText.contains("CASH")) {
            transactionDetails = "CASH";
        } else if (lineText.contains("TRADELINK ELE")) {
            transactionDetails = "TRADELINK ELE";
        } else if (lineText.contains("SUPPLY")) {
            transactionDetails = "SUPPLY";
        } else if (lineText.contains("HC12140735303388")) {
            transactionDetails = "HC12140735303388";
        } else if (lineText.contains("HSBC PRO FUND TRU HK")) {
            transactionDetails = "HSBC PRO FUND TRU HK";
        } else if (lineText.contains("HC1")) {
            transactionDetails = "HC1";
        } else if (lineText.contains("HK1")) {
            transactionDetails = "HK1";
        } else if (lineText.contains("HSBCNET MONTHLY FEE")) {
            transactionDetails = "HSBCNET MONTHLY FEE";
        } else if (lineText.contains("BIB-HOPSKY HK LTD")) {
            transactionDetails = "BIB-HOPSKY HK LTD";
        } else if (lineText.contains("TRANSACTION FEE")) {
            transactionDetails = "TRANSACTION FEE";
        } else if (lineText.contains("BIB- TO")) {
            transactionDetails = "BIB- TO";
        } else if (lineText.contains("DEBIT INTEREST")) {
            transactionDetails = "DEBIT INTEREST";
        } else if (lineText.contains("INLAND REVENUE")) {
            transactionDetails = "INLAND REVENUE";
        } else if (lineText.contains("ATM TRANSFER")) {
            transactionDetails = "ATM TRANSFER";
        } else if (lineText.contains("EPS")) {
            transactionDetails = "EPS";
        } else if (lineText.contains("TOYOMURA ELECTRONICS (HK")) {
            transactionDetails = "TOYOMURA ELECTRONICS (HK";
        } else if (lineText.contains("TO")) {
            transactionDetails = "TO";
        } else if (lineText.contains("SI - SWEEP")) {
            transactionDetails = "SI - SWEEP";
        } else if (lineText.contains("SWEEP")) {
            transactionDetails = "SWEEP";
        } else if (lineText.contains("E-ADVICE FEE")) {
            transactionDetails = "E-ADVICE FEE";
        } else if (lineText.contains("HONG KONG TRADE DEVELOPM")) {
            transactionDetails = "HONG KONG TRADE DEVELOPM";
        } else if (lineText.contains("THE FIRST PRINTING COMPA")) {
            transactionDetails = "THE FIRST PRINTING COMPA";
        } else if (lineText.contains("NICOLE ENVIRONMENT SERVI")) {
            transactionDetails = "NICOLE ENVIRONMENT SERVI";
        } else if (lineText.contains("HANGFUNG PRINTING (HK) C")) {
            transactionDetails = "HANGFUNG PRINTING (HK) C";
        } else if (lineText.contains("RECTANGLE (HK) CO,.LTD")) {
            transactionDetails = "RECTANGLE (HK) CO,.LTD";
        } else if (lineText.contains("HONGKONG TRADE DEVELOPME")) {
            transactionDetails = "HONGKONG TRADE DEVELOPME";
        } else if (lineText.contains("RECTANGLE(HK)CO,.LTD")) {
            transactionDetails = "RECTANGLE(HK)CO,.LTD";
        } else if (lineText.contains("SILVERWAYS INDUSTRIAL LT")) {
            transactionDetails = "SILVERWAYS INDUSTRIAL LT";
        } else if (lineText.contains("LUEN KEI PAD PRINT CO,.L")) {
            transactionDetails = "LUEN KEI PAD PRINT CO,.L";
        } else if (lineText.contains("SCHENKER INTERNATIONAL")) {
            transactionDetails = "SCHENKER INTERNATIONAL";
        } else if (lineText.contains("HANGFUNG PRINTING (HK)CO")) {
            transactionDetails = "HANGFUNG PRINTING (HK)CO";
        } else if (lineText.contains("KWAN CHUN PONG DICK")) {
            transactionDetails = "KWAN CHUN PONG DICK";
        } else if (lineText.contains("TRANS AM SEA FREIGHT")) {
            transactionDetails = "TRANS AM SEA FREIGHT";
        } else if (lineText.contains("PO WAH CO")) {
            transactionDetails = "PO WAH CO";
        } else if (lineText.contains("PRINTING")) {
            transactionDetails = "PRINTING";
        } else if (lineText.contains("REC")) {
            transactionDetails = "REC";
        } else if (lineText.contains("UNAUTHORIZED OD CHARGE")) {
            transactionDetails = "UNAUTHORIZED OD CHARGE";
        } else if (lineText.contains("DEBIT AS ADVISED")) {
            transactionDetails = "DEBIT AS ADVISED";
        } else if (lineText.contains("FH ELECTRONIC LTD")) {
            transactionDetails = "FH ELECTRONIC LTD";
        } else if (lineText.contains("CO SEARCH FEE")) {
            transactionDetails = "CO SEARCH FEE";
        } else if (lineText.contains("AC ADMIN FEE")) {
            transactionDetails = "AC ADMIN FEE";
        } else if (lineText.contains("ATM CUP")) {
            transactionDetails = "ATM CUP";
        } else if (lineText.contains("0.00%CHARGE")) {
            transactionDetails = "0.00%CHARGE";
        } else if (lineText.contains("ESS-67XUCI")) {
            transactionDetails = "ESS-67XUCI";
        } else if (lineText.contains("BIB- REFUND")) {
            transactionDetails = "BIB- REFUND";
        } else if (lineText.contains("REGISTRY FEE FOR COS")) {
            transactionDetails = "REGISTRY FEE FOR COS";
        } else if (lineText.contains("POS CUP")) {
            transactionDetails = "POS CUP";
        } else if (lineText.contains("CHG FOR AUDIT CONF O")) {
            transactionDetails = "CHG FOR AUDIT CONF O";
        } else if (lineText.contains("101020AA00076   B18B")) {
            transactionDetails = "101020AA00076 B18B";
        } else if (lineText.contains("2257ITSA2000127167DD")) {
            transactionDetails = "2257ITSA2000127167DD";
        } else if (lineText.contains("2257ITSA200011749A96")) {
            transactionDetails = "2257ITSA200011749A96";
        } else if (lineText.contains("2257ITSA200010679A89")) {
            transactionDetails = "2257ITSA200010679A89";
        } else if (lineText.contains("2257ITSA20000490B18B")) {
            transactionDetails = "2257ITSA20000490B18B";
        } else if (lineText.contains("KERRY FREIGHT (HK) LTD")) {
            transactionDetails = "KERRY FREIGHT (HK) LTD";
        } else if (lineText.contains("235920020247    AB70")) {
            transactionDetails = "235920020247    AB70";
        } else if (lineText.contains("235920020246    9A96")) {
            transactionDetails = "235920020246    9A96";
        } else if (lineText.contains("2257ITSA20000665AB73")) {
            transactionDetails = "2257ITSA20000665AB73";
        } else if (lineText.contains("235920020440    9A96")) {
            transactionDetails = "235920020440    9A96";
        } else if (lineText.contains("235920020441    B79F")) {
            transactionDetails = "235920020441    B79F";
        } else if (lineText.contains("101020AA00076   B79D")) {
            transactionDetails = "101020AA00076   B79D";
        } else if (lineText.contains("0180ILF2000093019A96")) {
            transactionDetails = "0180ILF2000093019A96";
        } else if (lineText.contains("0180ILF2000093019A89")) {
            transactionDetails = "0180ILF2000093019A89";
        } else if (lineText.contains("2257ITSA200013589A89")) {
            transactionDetails = "2257ITSA200013589A89";
        } else if (lineText.contains("NA2690544087")) {
            transactionDetails = "NA2690544087";
        } else if (lineText.contains("101020AA00117")) {
            transactionDetails = "101020AA00117";
        } else if (lineText.contains("KORCHINA LOGISTICS")) {
            transactionDetails = "KORCHINA LOGISTICS";
        } else if (lineText.contains("2257ITSA21000502AB70")) {
            transactionDetails = "2257ITSA21000502AB70";
        } else if (lineText.contains("FREGATE LOGISTICS LIMITE")) {
            transactionDetails = "FREGATE LOGISTICS LIMITE";
        } else if (lineText.contains("OTS TRANSPORT SERVICES")) {
            transactionDetails = "OTS TRANSPORT SERVICES";
        } else if (lineText.contains("0016723100198")) {
            transactionDetails = "0016723100198";
        } else if (lineText.contains("0180ILF200009301B79F")) {
            transactionDetails = "0180ILF200009301B79F";
        } else if (lineText.contains("2257ITSA2000106767DD")) {
            transactionDetails = "2257ITSA2000106767DD";
        } else if (lineText.contains("2257ITSA200011749A96")) {
            transactionDetails = "2257ITSA200011749A96";
        } else if (lineText.contains("2257ITSA210002549C76")) {
            transactionDetails = "2257ITSA210002549C76";
        } else if (lineText.contains("2257ITSA21000693AB70")) {
            transactionDetails = "2257ITSA21000693AB70";
        } else if (lineText.contains("2257ITSA210007699E4A")) {
            transactionDetails = "2257ITSA210007699E4A";
        } else if (lineText.contains("CTBC BANK CO.,LTD.,HONG 2,")) {
            transactionDetails = "CTBC BANK CO.,LTD.,HONG 2,";
        } else if (lineText.contains("GOLD/EXCHANGE CREDIT")) {
            transactionDetails = "GOLD/EXCHANGE CREDIT";
        }
        return transactionDetails;
    }

    /**
     * 处理金额
     *
     * @param lineText
     * @param one
     * @param transactionDetails
     * @param detailData
     * @param two
     */
    private static void handleAmount(String lineText, String one, String transactionDetails, BillDetailData detailData, String two) {
        if (StringUtils.hasText(two) && !two.matches(CommonUtil.AMOUNT_MATCHES)) {
            two = null;
        }

        if (transactionDetails.equals("BALANCE")) {
            if (StringUtils.hasText(one) && one.matches(CommonUtil.AMOUNT_MATCHES)) {
                detailData.setBalance(one);
            } else {
                detailData.setBalance(null);
            }
            detailData.setWithdrawal(null);
            detailData.setDeposit(null);
        } else if (transactionDetails.equals("PAYPAL")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("DROP-IN BOX CHQ DEPOSIT")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("DEPOSIT CORRECTION")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("DEPOSIT")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("WITHDRAWAL CORRECTION")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("WITHDRAWAL")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("REMIT MKTG CAMP")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("REM")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("REM HKD21838.92")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("CREDIT INTEREST")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.contains("CHARGES")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.contains("GOLD/EXCHANGE DEBIT")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("FROM")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("CREDIT AS ADVISED")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("CR TO")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("AUDIT CONFRMTION FEE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("TT REBATE")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("ACCT MAINT FEE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("DELIVERY CHARGE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("BLUBAN ELECTRON")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("REC")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("NB")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("REBATE")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("SUNRATE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("ISSUE OR REPLACE2")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("DEP94241117-16")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("PAYPAL HK LTD")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("MONTHLY SERVICE FEE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("BIB SEC DEVICE FEE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("CHEQUE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("CASH")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("TRADELINK ELE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("SUPPLY")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("HC12140735303388")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("HC1")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("HK1")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("HSBCNET MONTHLY FEE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("BIB-HOPSKY HK LTD")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("TRANSACTION FEE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("BIB- TO")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("DEBIT INTEREST")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("INLAND REVENUE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if (transactionDetails.equals("ATM TRANSFER")) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if (transactionDetails.equals("E-ADVICE FEE")) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("HONG KONG TRADE DEVELOPM".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("SI - SWEEP".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if ("THE FIRST PRINTING COMPA".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("NICOLE ENVIRONMENT SERVI".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("HANGFUNG PRINTING (HK) C".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("RECTANGLE (HK) CO,.LTD".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("SILVERWAYS INDUSTRIAL LT".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("RECTANGLE(HK)CO,.LTD".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("HONGKONG TRADE DEVELOPME".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("LUEN KEI PAD PRINT CO,.L".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("SCHENKER INTERNATIONAL".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("HANGFUNG PRINTING (HK)CO".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("PRINTING".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("KWAN CHUN PONG DICK".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("TRANS AM SEA FREIGHT".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("PO WAH CO".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("UNAUTHORIZED OD CHARGE".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("DEBIT AS ADVISED".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if ("FH ELECTRONIC LTD".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if ("CO SEARCH FEE".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("AC ADMIN FEE".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("ATM CUP".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("0.00%CHARGE".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("ESS-67XUCI".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if ("BIB- REFUND".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if ("REGISTRY FEE FOR COS".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("EPS".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("POS CUP".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("HSBC PRO FUND TRU HK".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("CHG FOR AUDIT CONF O".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("101020AA00076 B18B".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA200010679A89".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA20000490B18B".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("KERRY FREIGHT (HK) LTD".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("TOYOMURA ELECTRONICS (HK".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("235920020247    AB70".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("235920020440    9A96".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA20000665AB73".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("235920020441    B79F".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("101020AA00076   B79D".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("0180ILF2000093019A96".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("0180ILF2000093019A89".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA200013589A89".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("NA2690544087".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if ("2257ITSA2000127167DD".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA200011749A96".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("101020AA00117".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("KORCHINA LOGISTICS".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA21000502AB70".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("OTS TRANSPORT SERVICES".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("FREGATE LOGISTICS LIMITE".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("0016723100198".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("0180ILF200009301B79F".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA2000106767DD".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA200011749A96".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA210002549C76".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA21000693AB70".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("2257ITSA210007699E4A".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("CTBC BANK CO.,LTD.,HONG 2,".equals(transactionDetails)) {
            CommonUtil.addWithdrawal(one, detailData, two);
        } else if ("GOLD/EXCHANGE CREDIT".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else if ("DEPOSIT BIB- BALANCE".equals(transactionDetails)) {
            CommonUtil.addDeposit(one, detailData, two);
        } else {
            transactionDetails = CommonUtil.analyzeTransactionType(one, detailData, two);
        }
        if (null != transactionDetails) {
            detailData.setTransactionDetails(transactionDetails);
        }
    }

    /**
     * 测试pdf文件的创建
     *
     * @param args
     */
    public static void main(String[] args) {
        String filePath = "D://yalu//monthly//";  //这里先手动把绝对路径的文件夹给补上。
        LinkedList<String> filePaths = new LinkedList<>();
        recursionFile(filePath, filePaths);
        System.out.println("文件数量：" + filePaths.size());
        Map<String, BillData> map = new HashMap<>();
        String year = "";
        for (String fileName : filePaths) {
            int lastIndex = fileName.lastIndexOf("\\");
            year = fileName.substring(lastIndex + 1, lastIndex + 5);
            readPdf(fileName, year, map);
        }
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
        String time = LocalDateTime.now().format(dtf);
        String outFileName = "D:\\yalu\\" + year + "年_月结单_汇丰_" + time.substring(time.length() - 6) + ".xlsx";
        writerFils(map, outFileName);
    }

    /**
     * 写入文件
     *
     * @param map
     * @param outFileName
     */
    public static void writerFils(Map<String, BillData> map, String outFileName) {
        try {
            ExcelWriter excelWriter = EasyExcel.write(outFileName, BillDetailDataExcel.class).build();
            for (String key : map.keySet()) {
                BillData billData = map.get(key);
                String sheetName = "1";
                if ("Savings".equals(billData.getType())) {
                    sheetName = "月结单（" + billData.getCurrency() + "）";
                } else if ("Current".equals(billData.getType())) {
                    sheetName = "资金往来（" + billData.getCurrency() + "）";
                } else if ("CurrentAccount".equals(billData.getType())) {
                    sheetName = "资金往来（" + billData.getCurrency() + "）";
                }
                BigDecimal depositSum = new BigDecimal(0);
                BigDecimal withdrawalSum = new BigDecimal(0);
                List<BillDetailDataExcel> list = new ArrayList<>(billData.getDetailData().size());
                for (BillDetailData billDetailData : billData.getDetailData()) {
                    BillDetailDataExcel billDetailDataExcel = new BillDetailDataExcel();
                    BeanUtils.copyProperties(billDetailData, billDetailDataExcel);
                    if (billDetailData.getTransactionDetails().equals("BALANCE")) {
                        billDetailDataExcel.setTransactionDetails("承前转结");
                    } else if (billDetailData.getTransactionDetails().equals("PAYPAL HK LTD")) {
                        billDetailDataExcel.setTransactionDetails("转账收入");
                    } else if (billDetailData.getTransactionDetails().equals("PAYPAL")) {
                        billDetailDataExcel.setTransactionDetails("转入存款");
                    } else if (billDetailData.getTransactionDetails().equals("DROP-IN BOX CHQ DEPOSIT")) {
                        billDetailDataExcel.setTransactionDetails("办理箱支票存款");
                    } else if (billDetailData.getTransactionDetails().equals("DEPOSIT")) {
                        billDetailDataExcel.setTransactionDetails("存款");
                    } else if (billDetailData.getTransactionDetails().equals("WITHDRAWAL CORRECTION")) {
                        billDetailDataExcel.setTransactionDetails("提款更正");
                    } else if (billDetailData.getTransactionDetails().equals("WITHDRAWAL")) {
                        billDetailDataExcel.setTransactionDetails("提款");
                    } else if (billDetailData.getTransactionDetails().equals("REM")) {
                        billDetailDataExcel.setTransactionDetails("汇出汇款");
                    } else if (billDetailData.getTransactionDetails().equals("CREDIT INTEREST")) {
                        billDetailDataExcel.setTransactionDetails("利息收入");
                    } else if (billDetailData.getTransactionDetails().equals("CHARGES")) {
                        billDetailDataExcel.setTransactionDetails("银行手续费");
                    } else if (billDetailData.getTransactionDetails().equals("GOLD/EXCHANGE DEBIT")) {
                        billDetailDataExcel.setTransactionDetails("购入金/外汇支账");
                    } else if (billDetailData.getTransactionDetails().equals("FROM")) {
                        billDetailDataExcel.setTransactionDetails("转入存款");
                    } else if (billDetailData.getTransactionDetails().equals("REMIT MKTG CAMP")) {
                        billDetailDataExcel.setTransactionDetails("自动转账存入");
                    } else if (billDetailData.getTransactionDetails().equals("CREDIT AS ADVISED")) {
                        billDetailDataExcel.setTransactionDetails("转入存款");
                    } else if (billDetailData.getTransactionDetails().equals("CR TO")) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if (billDetailData.getTransactionDetails().equals("AUDIT CONFRMTION FEE")) {
                        billDetailDataExcel.setTransactionDetails("服务费");
                    } else if (billDetailData.getTransactionDetails().equals("TT REBATE")) {
                        billDetailDataExcel.setTransactionDetails("自动存款转入");
                    } else if (billDetailData.getTransactionDetails().equals("ACCT MAINT FEE")) {
                        billDetailDataExcel.setTransactionDetails("银行手续费");
                    } else if (billDetailData.getTransactionDetails().equals("DELIVERY CHARGE")) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if (billDetailData.getTransactionDetails().equals("BLUBAN ELECTRON")) {
                        billDetailDataExcel.setTransactionDetails("转入存款");
                    } else if (billDetailData.getTransactionDetails().equals("REC")) {
                        billDetailDataExcel.setTransactionDetails("汇款收入");
                    } else if (billDetailData.getTransactionDetails().equals("NB")) {
                        billDetailDataExcel.setTransactionDetails("转账支出");
                    } else if (billDetailData.getTransactionDetails().equals("REBATE")) {
                        billDetailDataExcel.setTransactionDetails("自动转账存入");
                    } else if (billDetailData.getTransactionDetails().equals("SUNRATE")) {
                        billDetailDataExcel.setTransactionDetails("转账支出");
                    } else if (billDetailData.getTransactionDetails().equals("ISSUE OR REPLACE2")) {
                        billDetailDataExcel.setTransactionDetails("自动转账支出");
                    } else if (billDetailData.getTransactionDetails().equals("DEP94241117-16")) {
                        billDetailDataExcel.setTransactionDetails("转账支出");
                    } else if (billDetailData.getTransactionDetails().equals("MONTHLY SERVICE FEE")) {
                        billDetailDataExcel.setTransactionDetails("服务月费");
                    } else if (billDetailData.getTransactionDetails().equals("BIB SEC DEVICE FEE")) {
                        billDetailDataExcel.setTransactionDetails("自动转账支取");
                    } else if (billDetailData.getTransactionDetails().equals("CHEQUE")) {
                        billDetailDataExcel.setTransactionDetails("支票支出");
                    } else if (billDetailData.getTransactionDetails().equals("CASH")) {
                        billDetailDataExcel.setTransactionDetails("现金提款");
                    } else if (billDetailData.getTransactionDetails().equals("TRADELINK ELE")) {
                        billDetailDataExcel.setTransactionDetails("自助转账");
                    } else if (billDetailData.getTransactionDetails().equals("SUPPLY")) {
                        billDetailDataExcel.setTransactionDetails("转账收入");
                    } else if (billDetailData.getTransactionDetails().equals("HC12140735303388")) {
                        billDetailDataExcel.setTransactionDetails("转账收入");
                    } else if (billDetailData.getTransactionDetails().equals("HC1")) {
                        billDetailDataExcel.setTransactionDetails("转账支出");
                    } else if (billDetailData.getTransactionDetails().equals("HK1")) {
                        billDetailDataExcel.setTransactionDetails("转账收入");
                    } else if (billDetailData.getTransactionDetails().equals("HSBCNET MONTHLY FEE")) {
                        billDetailDataExcel.setTransactionDetails("自动转账");
                    } else if (billDetailData.getTransactionDetails().equals("BIB-HOPSKY HK LTD")) {
                        billDetailDataExcel.setTransactionDetails("存入");
                    } else if (billDetailData.getTransactionDetails().equals("TRANSACTION FEE")) {
                        billDetailDataExcel.setTransactionDetails("交易收费");
                    } else if (billDetailData.getTransactionDetails().equals("BIB- TO")) {
                        billDetailDataExcel.setTransactionDetails("提款");
                    } else if (billDetailData.getTransactionDetails().equals(" NICOLE ENVIRONMENT SERVI")) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if (billDetailData.getTransactionDetails().equals("DEBIT INTEREST")) {
                        billDetailDataExcel.setTransactionDetails("利息支出");
                    } else if (billDetailData.getTransactionDetails().equals("INLAND REVENUE")) {
                        billDetailDataExcel.setTransactionDetails("交费支出");
                    } else if (billDetailData.getTransactionDetails().equals("ATM TRANSFER")) {
                        billDetailDataExcel.setTransactionDetails("柜员机转账存入");
                    } else if (billDetailData.getTransactionDetails().equals("NICOLE ENVIRONMENT SERVI")) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if (billDetailData.getTransactionDetails().equals("THE FIRST PRINTING COMPA")) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if (billDetailData.getTransactionDetails().equals("HANGFUNG PRINTING (HK) C")) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("RECTANGLE (HK) CO,.LTD".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("SILVERWAYS INDUSTRIAL LT".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("RECTANGLE(HK)CO,.LTD".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("HONGKONG TRADE DEVELOPME".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("LUEN KEI PAD PRINT CO,.L".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("SCHENKER INTERNATIONAL".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("HANGFUNG PRINTING (HK)CO".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("PRINTING".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("KWAN CHUN PONG DICK".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("TRANS AM SEA FREIGHT".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("PO WAH CO".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账支取");
                    } else if ("UNAUTHORIZED OD CHARGE".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("未经授权透支收费");
                    } else if ("DEBIT AS ADVISED".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账收入");
                    } else if ("FH ELECTRONIC LTD".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账收入");
                    } else if ("CO SEARCH FEE".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("银行手续费");
                    } else if ("AC ADMIN FEE".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("银行手续费");
                    } else if ("ATM CUP".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("柜员机现金提款");
                    } else if ("0.00%CHARGE".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("柜员机现金提款");
                    } else if ("ESS-67XUCI".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("转账收入");
                    } else if ("BIB- REFUND".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("存款");
                    } else if ("HSBC PRO FUND TRU HK".equals(billDetailData.getTransactionDetails())) {
                        billDetailDataExcel.setTransactionDetails("自动转账支出");
                    } else {
                        if (StringUtils.hasText(billDetailData.getDeposit())) {
                            billDetailDataExcel.setTransactionDetails("存款");
                        } else if (StringUtils.hasText(billDetailData.getWithdrawal())) {
                            billDetailDataExcel.setTransactionDetails("提款");
                        } else if (StringUtils.hasText(billDetailData.getBalance())) {
                            billDetailDataExcel.setTransactionDetails("结余");
                        }
                    }
                    if (StringUtils.hasText(billDetailData.getDeposit())) {
                        depositSum = depositSum.add(new BigDecimal(billDetailData.getDeposit()));
                    }
                    if (StringUtils.hasText(billDetailData.getWithdrawal())) {
                        withdrawalSum = withdrawalSum.add(new BigDecimal(billDetailData.getWithdrawal()));
                    }
                    list.add(billDetailDataExcel);
                }
                if (0 < list.size()) {
                    BillDetailDataExcel firstData = list.stream().findFirst().get();
                    String balance = firstData.getBalance();
                    BigDecimal balanceDecimal = new BigDecimal(0);
                    if (StringUtils.hasText(balance)) {
                        if (StringUtils.hasText(firstData.getDeposit()) || StringUtils.hasText(firstData.getDeposit())) {
                            balance = "0";
                        } else {
                            balanceDecimal = balanceDecimal.add(new BigDecimal(balance));
                        }
                    }
                    balanceDecimal = balanceDecimal.add(depositSum).subtract(withdrawalSum);
                    BillDetailDataExcel billDetailDataExcel = new BillDetailDataExcel();
                    billDetailDataExcel.setDate("智能校对助手校对结果");
                    billDetailDataExcel.setTransactionDetails("上期结余：" + balance);
                    billDetailDataExcel.setDeposit("总收入：" + depositSum.toString());
                    billDetailDataExcel.setWithdrawal("总支出：" + withdrawalSum.toString());
                    billDetailDataExcel.setBalance(balanceDecimal.toString());
                    list.add(billDetailDataExcel);
                }
                WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).build();
                excelWriter.write(list, writeSheet);
            }
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            System.out.println("文件写入失败：" + e.getMessage());
        }
    }

    /**
     * 递归文件
     *
     * @param path
     * @param filePaths
     */
    public static void recursionFile(String path, LinkedList<String> filePaths) {
        if (StringUtils.hasText(path)) {
            File file = new File(path);
            for (File f : file.listFiles()) {
                if (f.isDirectory()) {
                    recursionFile(f.getPath(), filePaths);
                } else {
                    if (f.getPath().toLowerCase().contains(".pdf")) {
                        filePaths.add(f.getPath());
                    }
                }
            }
        }
    }

}
